package com.hqhsc.module.common.dao;

import com.hqhsc.module.common.domain.*;
//import com.hqhsc.module.customermanage.domain.CustomerInfo;
//import com.hqhsc.module.customermanage.domain.CustomerInfoRowMapper;
import com.hqhsc.module.login.domain.User;
import com.hqhsc.module.login.domain.UserRowMapper;
import com.hqhsc.module.manage.activity.domain.Activity;
import com.hqhsc.module.manage.activity.domain.ActivityRowMapper;
import com.hqhsc.module.manage.activity.domain.ActivityTemplet;
import com.hqhsc.module.manage.activity.domain.ActivityTempletRowMapper;
import com.hqhsc.module.manage.channel.domain.Channel;
import com.hqhsc.module.manage.channel.domain.ChannelRowMapper;
import com.hqhsc.module.manage.system.domain.Org;
import com.hqhsc.module.manage.system.domain.OrgRowMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Repository;

import com.hqhsc.core.dao.BaseJdbcDaoImpl;

import java.util.List;


@Repository
public class CommonDao extends BaseJdbcDaoImpl{

	final Logger logger = LoggerFactory.getLogger(CommonDao.class);

	/**
	 * 获取序列
	 * @param seq_name
	 * @return
	 */
	public long getNextVal(String seq_name) {
		String sql="select nextval(?)";
		Object[] o = new Object[] {seq_name};
		logger.info(sql.replaceAll("\\?", "{}"), o);
		return this.getJdbcTemplate().queryForLong(sql , o);
	}

    /**
     * 获取某一用户的角色列表
     * @param user_id
     * @return
     */
    public List<UserRole> getUserRoleList(long user_id) {
        String sql="select a.* , b.org_name , c.role_name , d.user_name" +
                " from user_role a , sys_org b , sys_roles c , users d" +
                " where a.org_id = b.org_id and a.role_id = c.role_id and a.user_id = d.user_id" +
                " and a.user_id = ? and b.status = 1 order by a.role_id";
        Object[] o = new Object[] { user_id };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        return this.getJdbcTemplate().query(sql , o  , new UserRoleRowMapper());
    }

    /**
     * 根据某菜单id，获取该菜单的所有子菜单
     * @param  parent_meun_id
     * @return
     */
    public List<Menu> getMenuListByTopMenuid(long parent_meun_id) {
        String sql="select * from sys_menu where pid = ? and status = 1 order by menu_id";
        Object[] o = new Object[] { parent_meun_id };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        return this.getJdbcTemplate().query(sql , o  , new MenuRowMapper());
    }

    /**
     * 获取某一角色可操作的菜单
     * 包括顶层菜单，和子菜单
     * @param role_id
     * @return
     */
    public List<Menu> getRoleMenuList(long role_id) {
        String sql="select b.* from sys_role_menu a , sys_menu b where a.menu_id = b.menu_id and a.status = 1 and a.role_id = ? order by a.menu_id";
        Object[] o = new Object[] { role_id };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        return this.getJdbcTemplate().query(sql , o  , new MenuRowMapper());
    }

    /**
     * 根据user_id获取用户
     * @param user_id
     * @return
     */
    public User getUserById(long user_id) {
        String sql = "select * from users t WHERE t.user_id = ? ";
        Object[] o = new Object[] { user_id };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        try {
            return this.getJdbcTemplate().queryForObject(sql, o, new UserRowMapper());
        } catch (EmptyResultDataAccessException e) {
            logger.info("database is null for query.");
            return null;
        }
    }

    /**
     * 获取用户组织列表
     * @param user_id
     * @return
     */
    public List<Org> getUserOrgList(long user_id) {
        String sql="select distinct b.org_id ,  b.org_name , b.pid , b.org_type , b.org_province , b.org_city , b.org_country , " +
                "b.org_address , b.org_type_name , b.org_province_name , b.org_city_name , b.org_country_name  " +
                "from user_role a , sys_org b where a.org_id = b.org_id and a.user_id = ? and b.status = 0 order by b.pid , b.org_type";
        Object[] o = new Object[] { user_id };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        return this.getJdbcTemplate().query(sql , o  , new OrgRowMapper());
    }

    /**
     * 获取子组织
     * @param parent_org_ids
     * @return
     */
//    public List<Org> getUserSubOrgList(String parent_org_ids) {
//        String sql="SELECT b.* FROM sys_org b where pid in ("+parent_org_ids+") and status = 0 order by b.pid , b.org_type";
////        Object[] o = new Object[] { parent_org_ids };
//        Object[] o = new Object[] {};
//        logger.info(sql.replaceAll("\\?", "{}"), o);
//        return this.getJdbcTemplate().query(sql , o  , new OrgRowMapper());
//    }

    /**
     * 根据用户、角色，获取该用户所有组织列表
     * @param user_id
     * @param role_id
     * @return
     */
    public List<Org> getUserRoleOrgList(long user_id , long role_id) {
        String sql="select b.* from user_role a , sys_org b where a.org_id = b.org_id and a.user_id = ? and role_id = ? and b.status = 0 order by b.pid , b.org_type";
        Object[] o = new Object[] { user_id , role_id };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        return this.getJdbcTemplate().query(sql , o  , new OrgRowMapper());
    }

//    public List<CustomerInfo> getAllCustomerInfo() {
//        String sql="select * from customer_info ";
//        Object[] o = new Object[] { };
//        logger.info(sql.replaceAll("\\?", "{}"), o);
//        return this.getJdbcTemplate().query(sql , o  , new CustomerInfoRowMapper());
//    }

    public List<User> getAllEmployees() {
        String sql="select * from users where user_id != 100000 and status = 1 ";
        Object[] o = new Object[] { };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        return this.getJdbcTemplate().query(sql , o  , new UserRowMapper());
    }

    public List<Org> getSysAllOrgList() {
        String sql="select * from sys_org where status = 0 order by pid , org_id ";
        Object[] o = new Object[] { };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        return this.getJdbcTemplate().query(sql , o  , new OrgRowMapper());
    }

//    public List<CustomerInfo> getCertificateNoByNameAndDn(String customer_name , String customer_dn , String certificate_type) {
//        String sql ;
//        Object[] o ;
//        if(StringUtils.isBlank(certificate_type)) {
//            sql="select * from customer_info where customer_name = ? and customer_dn = ? ";
//            o = new Object[] { customer_name , customer_dn };
//        } else {
//            sql="select * from customer_info where customer_name = ? and customer_dn = ? and certificate_type = ? ";
//            o = new Object[] { customer_name , customer_dn , certificate_type  };
//        }
//
//        logger.info(sql.replaceAll("\\?", "{}"), o);
//        return this.getJdbcTemplate().query(sql , o  , new CustomerInfoRowMapper());
//    }

    public int checkIdCard(String certificate_no) {
        String sql = "select count(1) from customer_info where certificate_no = ? and certificate_type = '身份证' ";
        Object[] o = new Object[] { certificate_no };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        return this.getJdbcTemplate().queryForInt(sql , o);
    }

    public long fileImport(String origin_name , String file_name , String path , long create_by) {
        long file_import_id = this.getNextVal("FileImportSeq");
        String sql = "insert into file_import (origin_name , file_name , path , create_by) values (?,?,?,?)";
        Object[] o = new Object[]{ origin_name , file_name , path , create_by };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        long result = this.getJdbcTemplate().update(sql , o);
        if(result > 0) {
            result = file_import_id;
        }
        return result;
    }

    public Activity getSysActivityById(long activity_id) {
        try{
            String sql = "select * from activity where id = ?";
            Object[] o = new Object[]{ activity_id };
            logger.info(sql.replaceAll("\\?", "{}"), o);
            return this.getJdbcTemplate().queryForObject(sql , o , new ActivityRowMapper());
        } catch (Exception e) {
            logger.error(e.getMessage() , e);
            return null;
        }
    }

    public Channel getSysChannelById(long channel_id) {
        String sql = "select * from channel t WHERE t.id = ? ";
        Object[] o = new Object[] { channel_id };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        try {
            return this.getJdbcTemplate().queryForObject(sql, o, new ChannelRowMapper());
        } catch (EmptyResultDataAccessException e) {
            logger.info("database channel is null for query.");
            return null;
        }
    }

    public ActivityTemplet getActivityTemplet(long templet_id) {
        String sql = "select * from activity_templet t WHERE t.id = ? ";
        Object[] o = new Object[] { templet_id };
        logger.info(sql.replaceAll("\\?", "{}"), o);
        try {
            return this.getJdbcTemplate().queryForObject(sql, o, new ActivityTempletRowMapper());
        } catch (EmptyResultDataAccessException e) {
            logger.info("database activity_templet "+templet_id+" is null for query.");
            return null;
        }
    }
}
